﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Module;
using System.Data.SqlClient;
using Qmaker.DAL;
using System.Data;

namespace DLL
{
    public class DUserProfile
    {
        #region SQL语句

        #region 插入
        private const string SQL_INSERT = "INSERT INTO UserProfile(UserPassportId,UserImage,Email,Mobile,IDCardNum,IDCardImg,NickName,UserType,Gender,IsFamous,Birthday,Education,ProvinceId,CityId,VillageId,Adress,PersonalInCome,FamilyInCome,Credit,Dedication,LastUpdate,DateCreated,IDCardValidate,RealName,Bak1,Bak2,Bak3) VALUES (@UserPassportId,@UserImage,@Email,@Mobile,@IDCardNum,@IDCardImg,@NickName,@UserType,@Gender,@IsFamous,@Birthday,@Education, @ProvinceId,@CityId,@VillageId,@Adress,@PersonalInCome,@FamilyInCome,@Credit,@Dedication, @LastUpdate,@DateCreated,@IDCardValidate,@RealName,@Bak1,@Bak2,@Bak3)";
        #endregion

        #region 修改
        private const string SQL_UPDATE = "update UserProfile set UserImage=@UserImage,Email=@Email,Mobile=@Mobile,IDCardNum=@IDCardNum,IDCardImg=@IDCardImg,NickName=@NickName,	UserType=@UserType,Gender=@Gender,IsFamous=@IsFamous,	Birthday=@Birthday,	Education=@Education,	ProvinceId=@ProvinceId,	CityId=@CityId,	VillageId=@VillageId,	Adress=@Adress,PersonalInCome=@PersonalInCome,FamilyInCome=@FamilyInCome,Credit=@Credit,Dedication=@Dedication,LastUpdate=@LastUpdate,DateCreated=@DateCreated,IDCardValidate=@IDCardValidate,RealName=@RealName,Bak1=@Bak1,Bak2=@Bak2,Bak3=@Bak3 where UserPassportId=@UserPassportId";
        #endregion

        #region 删除
        private const string SQL_DELETE = "delete from UserProfile where  UserPassportId=@UserPassportId";
        #endregion

        #region 查询

        private const string SQL_SELECT_USEID = "select UserPassportId,UserImage,Email,Mobile,IDCardNum,IDCardImg,NickName,UserType,Gender,IsFamous,Birthday,Education,ProvinceId,CityId,VillageId,Adress,PersonalInCome,FamilyInCome,Credit,Dedication,LastUpdate,DateCreated,IDCardValidate,RealName,Bak1,Bak2,Bak3 from UserProfile where UserPassportId=@UserPassportId ";

        private const string SQL_SELECT_EMAIL = "select UserPassportId,UserImage,Email,Mobile,IDCardNum,IDCardImg,NickName,UserType,Gender,IsFamous,Birthday,Education,ProvinceId,CityId,VillageId,Adress,PersonalInCome,FamilyInCome,Credit,Dedication,LastUpdate,DateCreated,IDCardValidate,RealName,Bak1,Bak2,Bak3 from UserProfile where Email=@Email ";

        private const string SQL_SELECT_ALL = "select up.UserPassportId,UserImage,Email,Mobile,IDCardNum,IDCardImg,NickName,UserType,Gender,IsFamous,Birthday,Education,ProvinceId,CityId,VillageId,Adress,PersonalInCome,FamilyInCome,Credit,Dedication,LastUpdate,up.DateCreated,IDCardValidate,RealName,Bak1,Bak2,Bak3 from UserProfile up inner join UserPassport u on up.UserPassportId=u.UserPassportId and u.Status=1 order by u.LastLogin desc";

        private const string SQL_SELECT_ALL_TYPE = "select up.UserPassportId,UserImage,Email,Mobile,IDCardNum,IDCardImg,NickName,UserType,Gender,IsFamous,Birthday,Education,ProvinceId,CityId,VillageId,Adress,PersonalInCome,FamilyInCome,Credit,Dedication,LastUpdate,up.DateCreated,IDCardValidate,RealName,Bak1,Bak2,Bak3 from UserProfile up inner join UserPassport u on up.UserPassportId=u.UserPassportId and u.Status=1 where UserType=@UserType order by u.LastLogin desc";

        private const string SQL_SELECT_BY_PHONE = "select UserPassportId,UserImage,Email,Mobile,IDCardNum,IDCardImg,NickName,UserType,Gender,IsFamous,Birthday,Education,ProvinceId,CityId,VillageId,Adress,PersonalInCome,FamilyInCome,Credit,Dedication,LastUpdate,DateCreated,IDCardValidate,RealName,Bak1,Bak2,Bak3 from UserProfile where Mobile=@Mobile ";

        #endregion

        #endregion

        #region 公共接口

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="userProfileInfo"></param>
        /// <returns></returns>
        public bool Insert(UserProfileInfo userProfileInfo)
        {
            if (userProfileInfo == null)
                throw new ArgumentNullException("userProfileInfo");

            SqlParameter[] parms = GetInsertParms(userProfileInfo);
            //parms = SetInsertParmsCheck(parms);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, SQL_INSERT, parms));
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }
        /// <summary>
        /// 更新用户属性
        /// </summary>
        /// <param name="userProfileInfo"></param>
        /// <returns></returns>
        public bool Update(UserProfileInfo userProfileInfo)
        {
            if (userProfileInfo == null || userProfileInfo.UserPassportId.ToString().Length != 18)
                throw new ArgumentNullException("userProfileInfo");

            ;
            SqlParameter[] parms = GetInsertParms(userProfileInfo);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL_UPDATE, parms);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 获取用户属性
        /// </summary>
        /// <param name="userPassportId"></param>
        /// <returns></returns>
        public UserProfileInfo GetUserProfileInfo(long userPassportId)
        {
            UserProfileInfo userProfileInfo = new UserProfileInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@UserPassportId",SqlDbType.BigInt)
                };
            parms[0].Value = userPassportId;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_USEID, parms))
            {
                while (reader.Read())
                {
                    userProfileInfo = GetInstanceByDataReader(reader);

                }
            }
            return userProfileInfo;
        }

        /// <summary>
        /// 根据Email查找用户
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public UserProfileInfo GetUserProfileInfo(string email)
        {
            UserProfileInfo userProfileInfo = new UserProfileInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@Email",SqlDbType.VarChar)
                };
            parms[0].Value = email;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_EMAIL, parms))
            {
                while (reader.Read())
                {
                    userProfileInfo = GetInstanceByDataReader(reader);

                }
            }
            return userProfileInfo;
        }

        /// <summary>
        /// 获取所有有效用户的属性
        /// </summary>
        /// <returns></returns>
        public List<UserProfileInfo> GetALLUserProfile()
        {
            List<UserProfileInfo> userProfiles = new List<UserProfileInfo>();
            UserProfileInfo userProfile = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ALL, null))
            {
                while (reader.Read())
                {
                    userProfile = GetInstanceByDataReader(reader);
                    if (userProfile != null && userProfile.UserPassportId != 0)
                        userProfiles.Add(userProfile);
                }
            }


            return userProfiles;
        }
        /// <summary>
        /// 根据用户类型获取有效用户属性
        /// </summary>
        /// <param name="userType"></param>
        /// <returns></returns>
        public List<UserProfileInfo> GetALLUserProfileByUserType(int userType)
        {
            List<UserProfileInfo> userProfiles = new List<UserProfileInfo>();
            SqlParameter[] parms = {
                    new SqlParameter("@UserType",SqlDbType.BigInt)
                };
            parms[0].Value = userType;
            UserProfileInfo userProfile = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ALL_TYPE, parms))
            {
                while (reader.Read())
                {
                    userProfile = GetInstanceByDataReader(reader);
                    if (userProfile != null && userProfile.UserPassportId != 0)
                        userProfiles.Add(userProfile);
                }
            }


            return userProfiles;
        } 

        #endregion

        #region 私有接口
        

        private SqlParameter[] GetInsertParms(UserProfileInfo userProfileInfo)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@UserImage", SqlDbType.VarChar,30),
					new SqlParameter("@Email", SqlDbType.VarChar,100),
					new SqlParameter("@Mobile", SqlDbType.VarChar,20),
					new SqlParameter("@IDCardNum", SqlDbType.VarChar,50),
					new SqlParameter("@IDCardImg", SqlDbType.VarChar,30),
					new SqlParameter("@NickName", SqlDbType.NVarChar,20),
					new SqlParameter("@UserType", SqlDbType.Int,4),
					new SqlParameter("@Gender", SqlDbType.Int,4),
					new SqlParameter("@IsFamous", SqlDbType.Int,4),
					new SqlParameter("@Birthday", SqlDbType.DateTime),
					new SqlParameter("@Education", SqlDbType.Int,4),
					new SqlParameter("@ProvinceId", SqlDbType.Int,4),
					new SqlParameter("@CityId", SqlDbType.Int,4),
					new SqlParameter("@VillageId", SqlDbType.Int,4),
					new SqlParameter("@Adress", SqlDbType.NVarChar,250),
					new SqlParameter("@PersonalInCome", SqlDbType.Int,4),
					new SqlParameter("@FamilyInCome", SqlDbType.Int,4),
					new SqlParameter("@Credit", SqlDbType.NVarChar,20),
					new SqlParameter("@Dedication", SqlDbType.VarChar,20),
					new SqlParameter("@LastUpdate", SqlDbType.DateTime),
					new SqlParameter("@DateCreated", SqlDbType.DateTime),
					new SqlParameter("@UserPassportId", SqlDbType.BigInt,8),
                    new SqlParameter("@IDCardValidate", SqlDbType.Int,4),
                    new SqlParameter("@RealName",SqlDbType.NVarChar,20),
                    new SqlParameter("@Bak1", SqlDbType.VarChar,20),
                    new SqlParameter("@Bak2", SqlDbType.VarChar,20),
                    new SqlParameter("@Bak3", SqlDbType.VarChar,20)
                  };
            return SetInsertParms(parameters, userProfileInfo);
        }

        private SqlParameter[] SetInsertParms(SqlParameter[] parameters, UserProfileInfo userProfileInfo)
        {
            parameters[0].Value = userProfileInfo.UserImage;
            parameters[1].Value = userProfileInfo.Email;
            parameters[2].Value = userProfileInfo.Mobile;
            parameters[3].Value = userProfileInfo.IDCardNum;
            parameters[4].Value = userProfileInfo.IDCardImg;
            parameters[5].Value = userProfileInfo.NickName;
            parameters[6].Value = userProfileInfo.UserType;
            parameters[7].Value = userProfileInfo.Gender;
            parameters[8].Value = userProfileInfo.IsFamous;
            parameters[9].Value = userProfileInfo.Birthday;
            parameters[10].Value = userProfileInfo.Education;
            parameters[11].Value = userProfileInfo.ProvinceId;
            parameters[12].Value = userProfileInfo.CityId;
            parameters[13].Value = userProfileInfo.VillageId;
            parameters[14].Value = userProfileInfo.Adress;
            parameters[15].Value = userProfileInfo.PersonalInCome;
            parameters[16].Value = userProfileInfo.FamilyInCome;
            parameters[17].Value = userProfileInfo.Credit;
            parameters[18].Value = userProfileInfo.Dedication;
            parameters[19].Value = userProfileInfo.LastUpdate;
            parameters[20].Value = userProfileInfo.DateCreated;
            parameters[21].Value = userProfileInfo.UserPassportId;
            parameters[22].Value = userProfileInfo.IDCardValidate;
            parameters[23].Value = userProfileInfo.RealName;
            parameters[24].Value = userProfileInfo.Bak1;
            parameters[25].Value = userProfileInfo.Bak2;
            parameters[26].Value = userProfileInfo.Bak3;
            return SetInsertParmsCheck(parameters);
        }

        private UserProfileInfo GetInstanceByDataReader(SqlDataReader reader)
        {
            UserProfileInfo instance = new UserProfileInfo();
            //UserPassportId,UserImage,Email,Mobile,IDCardNum,IDCardImg,NickName,UserType,Gender,IsFamous,
            //Birthday,Education,ProvinceId,CityId,VillageId,Adress,PersonalInCome,FamilyInCome,Credit,Dedication,LastUpdate,DateCreated
            instance.UserPassportId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0);
            instance.UserImage = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
            instance.Email = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
            instance.Mobile = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
            instance.IDCardNum = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
            instance.IDCardImg = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
            instance.NickName = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
            instance.UserType = reader.IsDBNull(7) ? 0 : reader.GetInt32(7);
            instance.Gender = reader.IsDBNull(8) ? 1 : reader.GetInt32(8);
            instance.IsFamous = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
            instance.Birthday = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10);
            instance.Education = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
            instance.ProvinceId = reader.IsDBNull(12) ? 0 : reader.GetInt32(12);
            instance.CityId = reader.IsDBNull(13) ? 0 : reader.GetInt32(13);
            instance.VillageId = reader.IsDBNull(14) ? 0 : reader.GetInt32(14);
            instance.Adress = reader.IsDBNull(15) ? string.Empty : reader.GetString(15);
            instance.PersonalInCome = reader.IsDBNull(16) ? 0 : reader.GetInt32(16);
            instance.FamilyInCome = reader.IsDBNull(17) ? 0 : reader.GetInt32(17);
            instance.Credit = reader.IsDBNull(18) ? string.Empty : reader.GetString(18);
            instance.Dedication = reader.IsDBNull(19) ? string.Empty : reader.GetString(19);
            instance.LastUpdate = reader.IsDBNull(20) ? DateTime.MinValue : reader.GetDateTime(20);
            instance.DateCreated = reader.IsDBNull(21) ? DateTime.MinValue : reader.GetDateTime(21);
            instance.IDCardValidate = reader.IsDBNull(22) ? 0 : reader.GetInt32(22);
            instance.RealName = reader.IsDBNull(23) ? string.Empty : reader.GetString(23);

            instance.Bak1 = reader.IsDBNull(24) ? string.Empty : reader.GetString(24);
            instance.Bak2 = reader.IsDBNull(25) ? string.Empty : reader.GetString(25);
            instance.Bak3 = reader.IsDBNull(26) ? string.Empty : reader.GetString(26);
            return instance;
        }


        private SqlParameter[] SetInsertParmsCheck(SqlParameter[] parms)
        {
            for (int i = 0; i < parms.Length; i++)
            {
                if (parms[i].Value == null)
                {
                    parms[i].Value = DBNull.Value;
                }
                if (parms[i].DbType == DbType.DateTime && parms[i].Value.Equals(DateTime.MinValue))
                {
                    parms[i].Value = DBNull.Value;
                }
            }
            return parms;
        }
        
        public UserProfileInfo GetUserProfileInfoByPhone(string phone)
        {
            UserProfileInfo userProfileInfo = new UserProfileInfo();
            SqlParameter[] parms = {
                    new SqlParameter("@Mobile",SqlDbType.VarChar)
                };
            parms[0].Value = phone;

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_BY_PHONE, parms))
            {
                while (reader.Read())
                {
                    userProfileInfo = GetInstanceByDataReader(reader);
                }
            }
            return userProfileInfo;
        }
        #endregion
    }
}
